package com.useeinfo.framework.sugar.tools;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

/**
 * ClassName:ExcelFileParser <br/>
 * Function: TODO ADD FUNCTION. <br/>
 * Reason: TODO ADD REASON. <br/>
 * Date: 2015年12月29日 上午9:26:21 <br/>
 *
 * @author chiwei
 * @see
 * @since JDK 1.6
 */
public class ExcelFileParser {

    public static Workbook getWb(String path) {
        try {
            return WorkbookFactory.create(new File(path));
        } catch (Exception e) {
            throw new RuntimeException("读取EXCEL文件出错", e);
        }
    }

    public static Sheet getSheet(Workbook wb, int sheetIndex) {
        if (wb == null) {
            throw new RuntimeException("工作簿对象为空");
        }
        int sheetSize = wb.getNumberOfSheets();
        if (sheetIndex < 0 || sheetIndex > sheetSize - 1) {
            throw new RuntimeException("工作表获取错误");
        }
        return wb.getSheetAt(sheetIndex);
    }

    public static List<List<String>> getExcelRows(Sheet sheet, int startLine, int endLine) {
        List<List<String>> list = new ArrayList<List<String>>();
        // 如果开始行号和结束行号都是-1的话，则全表读取
        if (startLine == -1)
            startLine = 0;
        if (endLine == -1) {
            endLine = sheet.getLastRowNum() + 1;
        } else {
            endLine += 1;
        }
        for (int i = startLine; i < endLine; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                System.out.println("该行为空，直接跳过");
                continue;
            }
            int rowSize = row.getLastCellNum();
            List<String> rowList = new ArrayList<String>();
            for (int j = 0; j < rowSize; j++) {
                Cell cell = row.getCell(j);
                String temp = "";
                if (cell == null) {
                    System.out.println("该列为空，赋值双引号");
                    temp = "NULL";
                } else {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING:
                            temp = cell.getStringCellValue().trim();
                            temp = StringUtils.isEmpty(temp) ? "NULL" : temp;
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            temp = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            temp = String.valueOf(cell.getCellFormula().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                temp = DateTimeUtils.formatDateToStringWithTime(cell.getDateCellValue());
                            } else {
                                temp = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            temp = "NULL";
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            temp = "ERROR";
                            break;
                        default:
                            temp = cell.toString().trim();
                            break;
                    }
                }
                rowList.add(temp);
            }
            list.add(rowList);
        }
        return list;
    }

    public static void getDataFromExcel(String filePath) throws IOException {
        //String filePath = "E:\\123.xlsx";

        //判断是否为excel类型文件
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            System.out.println("文件不是excel类型");
        }

        FileInputStream fis = null;
        Workbook wookbook = null;
        Sheet sheet = null;
        try {
            //获取一个绝对地址的流
            fis = new FileInputStream(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            //2003版本的excel，用.xls结尾
            wookbook = new HSSFWorkbook(fis);//得到工作簿

        } catch (Exception ex) {
            //ex.printStackTrace();
            try {
                //2007版本的excel，用.xlsx结尾
                fis = new FileInputStream(filePath);
                wookbook = new XSSFWorkbook(fis);//得到工作簿
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }


        Map<String, PictureData> maplist = null;

        sheet = wookbook.getSheetAt(0);
        // 判断用07还是03的方法获取图片
        if (filePath.endsWith(".xls")) {
            maplist = getPictures1((HSSFSheet) sheet);
        } else if (filePath.endsWith(".xlsx")) {
            maplist = getPictures2((XSSFSheet) sheet);
        }
        try {
            printImg(maplist);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //得到一个工作表


        //获得表头
        Row rowHead = sheet.getRow(0);

        //判断表头是否正确
        System.out.println(rowHead.getPhysicalNumberOfCells());
        if (rowHead.getPhysicalNumberOfCells() != 5) {
            System.out.println("表头的数量不对!");
        }

        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        //要获得属性
        int studentid = 0;
        String studentname = "";
        String grade = "";
        String classes = "";
        String pic = "";
        //获得所有数据
        for (int i = 1; i <= totalRowNum; i++) {
            //获得第i行对象
            Row row = sheet.getRow(i);

            //获得获得第i行第0列的 String类型对象
            Cell cell = row.getCell((short) 0);
            studentid = (int) cell.getNumericCellValue();

            //获得一个数字类型的数据
            //studentname = (int) cell.getNumericCellValue();
            cell = row.getCell((short) 1);
            studentname = cell.getStringCellValue().toString();

            cell = row.getCell((short) 2);
            grade = cell.getStringCellValue().toString();

            cell = row.getCell((short) 3);
            classes = cell.getStringCellValue().toString();

            cell = row.getCell((short) 3);
            classes = cell.getStringCellValue().toString();

            System.out.println("学号：" + studentid + ",姓名：" + studentname + ",年级：" + grade + ",班级：" + classes + ",证件照：" + pic);
        }
        for (Map.Entry<String, PictureData> entry : maplist.entrySet()) {

            System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());

        }
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

    //图片写出
    public static void printImg(Map<String, PictureData> sheetList) throws IOException {

        //for (Map<String, PictureData> map : sheetList) {
        Object key[] = sheetList.keySet().toArray();
        for (int i = 0; i < sheetList.size(); i++) {
            // 获取图片流
            PictureData pic = sheetList.get(key[i]);
            // 获取图片索引
            String picName = key[i].toString();
            // 获取图片格式
            String ext = pic.suggestFileExtension();

            byte[] data = pic.getData();

            //图片保存路径
            FileOutputStream out = new FileOutputStream("c:\\model\\pic" + picName + "." + ext);
            out.write(data);
            out.close();
        }
        // }

    }


    public static void copyExcel(String fromexcel, String newexcel) {
        HSSFWorkbook wb = null;
        FileInputStream fis = null;
        FileOutputStream fos = null;
        try {
            fis = new FileInputStream(fromexcel);
            fos = new FileOutputStream(newexcel);
            wb = new HSSFWorkbook(fis);
            /*HSSFSheet fromsheet = wb.getSheetAt(0);
            Row row = fromsheet.getRow(1);
            Cell cell = row.getCell(2);
            cell.setCellValue("");*/
            wb.write(fos);
            fis.close();
            fos.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null)
                    fis.close();
                if (fos != null)
                    fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    public static void main(String[] args) throws Exception {
//        getDataFromExcel("C:\\model\\螺杆式空压机组点检表.xls");
        copyExcel("C:\\model\\螺杆式空压机组点检表.xls","C:\\model\\3.xls");
    }


   /* public static void main(String a[]) {
        String path = "C:\\model\\2.xls";
        Workbook wb = getWb(path);
        List<List<String>> list = getExcelRows(getSheet(wb, 0), -1, -1);
        for (int i = 0; i < list.size(); i++) {
            List<String> row = list.get(i);
            for (int j = 0; j < row.size(); j++) {
                System.out.print(row.get(j) + "\t");
            }
            System.out.println();
        }
    }*/

}
